/**
 * MenuDAO.java
 * Nov 7, 2013
 * Copyright: duylqkse60752@fpt.edu.vn
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DBUltilities.ConnectDB;
import DTO.DietDTO;
import DTO.MenuDTO;

/**
 * @author Lubu
 * @version 1.0
 */
public class MenuDAO {
	public void get(MenuDTO menu) {
		if (menu.getId() != 0) {
			Connection conn = null;
			PreparedStatement preparedStatement = null;

			String sqlStatement = "SELECT * FROM MENU WHERE ID = ? ";

			try {
				conn = ConnectDB.getConnection();
				preparedStatement = conn.prepareStatement(sqlStatement);
				preparedStatement.setInt(1, menu.getId());
				ResultSet rs = preparedStatement.executeQuery();

				while (rs.next()) {
					menu.setId((Integer) rs.getObject(1));
					menu.setName(new String(rs.getObject(2) == null ? "" : rs
							.getObject(2).toString()));
					menu.setDescription(new String(rs.getObject(3) == null ? ""
							: rs.getObject(3).toString()));
					menu.setImgPath(new String(rs.getObject(4) == null ? ""
							: rs.getObject(4).toString()));

					DietDTO diet = new DietDTO();
					diet.setId(Integer.parseInt(rs.getObject(5) == null ? "0"
							: rs.getObject(5).toString()));
					menu.setDiet(diet);

					menu.setNumberday(Integer.parseInt(rs.getObject(6) == null ? "0"
							: rs.getObject(6).toString()));
					menu.setNumbermeal(Integer.parseInt(rs.getObject(7) == null ? "0"
							: rs.getObject(7).toString()));
					menu.setCalo(Integer.parseInt(rs.getObject(8) == null ? "0"
							: rs.getObject(8).toString()));
				}
			} catch (SQLException e) {
			} finally {
				if (preparedStatement != null) {
					try {
						preparedStatement.close();
					} catch (SQLException e) {
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
					}
				}
			}
		}
	}

	public void add(MenuDTO menu) {

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "INSERT INTO MENU (NAME, DESCRIPTION, IMGPATH, DIETID, NUMBERDAY, NUMBERMEAL, CALO) VALUES"
				+ "(?,?,?,?,?,?,?)";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, menu.getName());
			preparedStatement.setString(2, menu.getDescription());
			String imgPath = menu.getImgPath().replace("\\", "/");
			preparedStatement.setString(3, imgPath);
			preparedStatement.setInt(4, menu.getDiet().getId());
			preparedStatement.setInt(5, menu.getNumberday());
			preparedStatement.setInt(6, menu.getNumbermeal());
			preparedStatement.setInt(7, menu.getCalo());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void update(MenuDTO menu) {

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "UPDATE MENU SET NAME = ?, DESCRIPTION = ?, IMGPATH = ?, DIETID = ?, NUMBERDAY = ?, NUMBERMEAL = ?, CALO = ? WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, menu.getName());
			preparedStatement.setString(2, menu.getDescription());
			String imgPath = menu.getImgPath().replace("\\", "/");
			preparedStatement.setString(3, imgPath);
			preparedStatement.setInt(4, menu.getDiet().getId());
			preparedStatement.setInt(5, menu.getNumberday());
			preparedStatement.setInt(6, menu.getNumbermeal());
			preparedStatement.setInt(7, menu.getCalo());
			preparedStatement.setInt(8, menu.getId());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void delete(int id) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "DELETE FROM MENU WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, id);

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public ArrayList<MenuDTO> select() {
		Connection conn = null;
		Statement statement = null;
		ArrayList<MenuDTO> list = new ArrayList<MenuDTO>();

		String sqlStatement = "SELECT * FROM MENU";
		try {
			conn = ConnectDB.getConnection();
			statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlStatement);
			int id;
			String name;
			String description;
			String imgPath;
			int calo;
			int numberday;
			int numbermeal;

			while (rs.next()) {
				id = Integer.parseInt(rs.getObject(1) == null ? "0" : rs
						.getObject(1).toString());
				name = new String(rs.getObject(2) == null ? "" : rs
						.getObject(2).toString());
				description = new String(rs.getObject(3) == null ? "" : rs
						.getObject(3).toString());
				imgPath = new String(rs.getObject(4) == null ? "" : rs
						.getObject(4).toString());

				DietDTO diet = new DietDTO();
				diet.setId(Integer.parseInt(rs.getObject(5) == null ? "0" : rs
						.getObject(5).toString()));

				numberday = Integer.parseInt(rs.getObject(6) == null ? "0" : rs
						.getObject(6).toString());
				numbermeal = Integer.parseInt(rs.getObject(7) == null ? "0"
						: rs.getObject(7).toString());
				calo = Integer.parseInt(rs.getObject(8) == null ? "0" : rs
						.getObject(8).toString());

				MenuDTO menu = new MenuDTO(id, diet, name, description,
						imgPath, calo, numberday, numbermeal);
				list.add(menu);
			}
		} catch (Exception e) {
			return null;
		}
		return list;
	}

	// public ArrayList<MenuDTO> selectByMenuGroup(int menuGroupId) {
	// Connection conn = null;
	// PreparedStatement preparedStatement = null;
	// ArrayList<MenuDTO> list = new ArrayList<MenuDTO>();
	//
	// String sqlStatement = "SELECT * FROM MENU WHERE MENUGROUPID = ?";
	// try {
	// conn = ConnectDB.getConnection();
	// preparedStatement = conn.prepareStatement(sqlStatement);
	// preparedStatement.setInt(1, menuGroupId);
	// ResultSet rs = preparedStatement.executeQuery();
	// int id;
	// String name;
	// int calo;
	//
	// while (rs.next()) {
	// id = (Integer) rs.getObject(1);
	// name = new String(rs.getObject(2) == null ? "" : rs
	// .getObject(2).toString());
	// calo = (Integer) rs.getObject(3);
	// MenuGroupDAO mgDAO = new MenuGroupDAO();
	// MenuGroupDTO mgDTO = new MenuGroupDTO();
	// mgDTO.setId(Integer.parseInt(rs.getObject(5) == null ? "0" : rs
	// .getObject(5).toString()));
	// mgDAO.get(mgDTO);
	//
	// MenuDTO menu = new MenuDTO(id, name, calo, mgDTO);
	// list.add(menu);
	// }
	// } catch (Exception e) {
	// return null;
	// } finally {
	// if (preparedStatement != null) {
	// try {
	// preparedStatement.close();
	// } catch (SQLException e) {
	// }
	// }
	// if (conn != null) {
	// try {
	// conn.close();
	// } catch (SQLException e) {
	// }
	// }
	// }
	// return list;
	// }
}
